# coding: utf-8
from fake_useragent import UserAgent
import urllib.request
import requests
import json
import pymysql

#获取数据
def getUrl(url):
    ua = UserAgent()
    headers={}
    headers['User-Agent'] = ua.random
    #一次get获取(str)
    r = requests.get(url,headers = headers).text
    return r

def data_load(r):
    #二次json加载解析(dict)
    payload = json.loads(r)['payload']
    #三次列表选取(list)
    dataLists = payload['teams']
    for dataList in dataLists:
        profile = dataList['profile']
        newsObjs = [profile.get('name'),profile.get('code'),profile.get('city')]
        # print(newsObjs)
        # return newsObjs

#连接数据库
def executeSql(sql,values):
    conn = pymysql.connect(host="localhost", user="root",
                         password="root", db="test", port=3306, charset='utf8')
    # 使用cursor()方法获取操作游标
    cur = conn.cursor()
    #执行sql语句
    effect = cur.execute(sql,values)
    #提交
    conn.commit()
    #关闭游标
    cur.close()
    #关闭连接
    conn.close()

#插入数据
def insert(newsObjs):
    sql = "insert into test(name,code,city) VALUES ('%s','%s','%s')"
    executeSql(sql=sql, values=newsObjs)
if __name__ == '__main__':
    url = 'http://china.nba.com/static/data/league/teamstats_All_All_2017_4.json'
    getUrl(url)
    insert()